USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_median_val') BEGIN
	DROP  FUNCTION  tsql.fn_median_val
	PRINT '<<< DROP FUNCTION tsql.fn_median_val - Completed with SUCCESS >>>'
END
GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.fn_median_val
Type:			User defined function 
Owner:					
Description:	Returns the (median) middle value of a  coma sepparated list of numeric values 
Parameters:		@list = list of numeric values comma delimited
Dependens on:  	fn_split
Usage:			SELECT [tsql].[fn_median_val] ('1,2,2,2,3,4,5')
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_median_val&referringTitle=Documentation		
Version:		20120905
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		09/05/2012 - Adrian E Dudau - Function created
================================================================================================*/

CREATE FUNCTION [tsql].[fn_median_val] (@list NVARCHAR(MAX))
RETURNS FLOAT 
AS

BEGIN /*FUNCTION*/
	DECLARE @RetVal FLOAT = 0; 

	WITH cteList AS 
		(SELECT id = ROW_NUMBER() OVER(ORDER BY CAST(item_value AS FLOAT))
			  , item_value = CAST(item_value AS FLOAT) 
			  , rcnt = COUNT(item_value) OVER(PARTITION BY 1) 
		 FROM [tsql].[fn_split](@list,',') 
		 )

	SELECT @RetVal = AVG(item_value) 
	FROM cteList 
	WHERE id BETWEEN CEILING(rcnt/2.00) AND (CASE WHEN (rcnt % 2 = 1) THEN CEILING(rcnt/2.00) ELSE CEILING(rcnt/2.00) + 1 END)

	RETURN @RetVal 
END /*FUNCTION*/

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_median_val') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_median_val - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_median_val - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


